package com.clouds.wms.dao;

import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import com.clouds.wms.bean.Purmg;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;
import com.clouds.wms.util.PagesBean;
import com.clouds.wms.util.SysParams;

@Repository("purmgDao")
public class PurmgDaoImpl implements PurmgDao {
	private Log log = LogFactory.getLog(getClass());
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	@Override
	public PagesBean getPurmgList(String mgaa, String mgab, String mgac,String startMgad, String endMgad, int offset, int pageSize)	throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Purmg> list = new ArrayList<Purmg>();
		boolean hasWhere = false;
		StringBuffer sql = new StringBuffer("SELECT * FROM purmg ");
		if(mgaa != null && !"".equals(mgaa)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mgaa = '" + Function.formatSQLString(mgaa) + "'");
		}
		if(mgab != null && !"".equals(mgab)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mgab = '" + Function.formatSQLString(mgab) + "'");
		}
		if(mgac != null && !"".equals(mgac)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mgac = '" + Function.formatSQLString(mgac) + "'");
		}else{
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mgac in ('" + SysParams.AUDIT_YARDS_NOT_AUDIT + "','S') ");
		}
		if(startMgad != null && !"".equals(startMgad)) {
			sql.append( " mgad >= '" + Function.formatSQLString(startMgad) + "' ");
		}
		if(endMgad != null && !"".equals(endMgad)) {
			sql.append(" mgad <= '" + Function.formatSQLString(endMgad) + "' ");
		}
		sql.append(" ORDER BY mgab DESC");
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Purmg>(Purmg.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public Purmg getPurmg(String mgaa,String mgab) throws DatabaseException {
		String sql = "SELECT * FROM purmg WHERE mgaa=? and mgab = ? ";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{mgaa,mgab}, new BeanPropertyRowMapper<Purmg>(Purmg.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public int savePurmg(Purmg purmg) throws DatabaseException {
		String sql = "INSERT INTO purmg (mgaa,mgab,mgad) VALUES (:mgaa,:mgab,:mgad)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(purmg);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}
	@Override
	public void delPurmgByAaAb(String mgaa, String mgab)throws DatabaseException {
		String sql1 = "delete from purmg where mgaa = ? and mgab = ?";
		namedParameterJdbcTemplate.getJdbcOperations().update(sql1, new Object[]{mgaa,mgab});
	}

}
